<!doctype html>
<html style='font-size:20px !important'>
<head>
<meta charset='UTF-8'><meta name='viewport' content='width=device-width initial-scale=1'>
<title>基础章节-02-企业数据库存储服务</title><style type='text/css'>html {overflow-x: initial !important;}:root { --bg-color:#ffffff; --text-color:#333333; --select-text-bg-color:#B5D6FC; --select-text-font-color:auto; --monospace:"Lucida Console",Consolas,"Courier",monospace; }
html { font-size: 14px; background-color: var(--bg-color); color: var(--text-color); font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; -webkit-font-smoothing: antialiased; }
body { margin: 0px; padding: 0px; height: auto; bottom: 0px; top: 0px; left: 0px; right: 0px; font-size: 1rem; line-height: 1.42857; overflow-x: hidden; background: inherit; tab-size: 4; }
iframe { margin: auto; }
a.url { word-break: break-all; }
a:active, a:hover { outline: 0px; }
.in-text-selection, ::selection { text-shadow: none; background: var(--select-text-bg-color); color: var(--select-text-font-color); }
#write { margin: 0px auto; height: auto; width: inherit; word-break: normal; overflow-wrap: break-word; position: relative; white-space: normal; overflow-x: visible; padding-top: 40px; }
#write.first-line-indent p { text-indent: 2em; }
#write.first-line-indent li p, #write.first-line-indent p * { text-indent: 0px; }
#write.first-line-indent li { margin-left: 2em; }
.for-image #write { padding-left: 8px; padding-right: 8px; }
body.typora-export { padding-left: 30px; padding-right: 30px; }
.typora-export .footnote-line, .typora-export li, .typora-export p { white-space: pre-wrap; }
@media screen and (max-width: 500px) {
  body.typora-export { padding-left: 0px; padding-right: 0px; }
  #write { padding-left: 20px; padding-right: 20px; }
  .CodeMirror-sizer { margin-left: 0px !important; }
  .CodeMirror-gutters { display: none !important; }
}
#write li > figure:last-child { margin-bottom: 0.5rem; }
#write ol, #write ul { position: relative; }
img { max-width: 100%; vertical-align: middle; }
button, input, select, textarea { color: inherit; font: inherit; }
input[type="checkbox"], input[type="radio"] { line-height: normal; padding: 0px; }
*, ::after, ::before { box-sizing: border-box; }
#write h1, #write h2, #write h3, #write h4, #write h5, #write h6, #write p, #write pre { width: inherit; }
#write h1, #write h2, #write h3, #write h4, #write h5, #write h6, #write p { position: relative; }
p { line-height: inherit; }
h1, h2, h3, h4, h5, h6 { break-after: avoid-page; break-inside: avoid; orphans: 2; }
p { orphans: 4; }
h1 { font-size: 2rem; }
h2 { font-size: 1.8rem; }
h3 { font-size: 1.6rem; }
h4 { font-size: 1.4rem; }
h5 { font-size: 1.2rem; }
h6 { font-size: 1rem; }
.md-math-block, .md-rawblock, h1, h2, h3, h4, h5, h6, p { margin-top: 1rem; margin-bottom: 1rem; }
.hidden { display: none; }
.md-blockmeta { color: rgb(204, 204, 204); font-weight: 700; font-style: italic; }
a { cursor: pointer; }
sup.md-footnote { padding: 2px 4px; background-color: rgba(238, 238, 238, 0.7); color: rgb(85, 85, 85); border-radius: 4px; cursor: pointer; }
sup.md-footnote a, sup.md-footnote a:hover { color: inherit; text-transform: inherit; text-decoration: inherit; }
#write input[type="checkbox"] { cursor: pointer; width: inherit; height: inherit; }
figure { overflow-x: auto; margin: 1.2em 0px; max-width: calc(100% + 16px); padding: 0px; }
figure > table { margin: 0px !important; }
tr { break-inside: avoid; break-after: auto; }
thead { display: table-header-group; }
table { border-collapse: collapse; border-spacing: 0px; width: 100%; overflow: auto; break-inside: auto; text-align: left; }
table.md-table td { min-width: 32px; }
.CodeMirror-gutters { border-right: 0px; background-color: inherit; }
.CodeMirror-linenumber { user-select: none; }
.CodeMirror { text-align: left; }
.CodeMirror-placeholder { opacity: 0.3; }
.CodeMirror pre { padding: 0px 4px; }
.CodeMirror-lines { padding: 0px; }
div.hr:focus { cursor: none; }
#write pre { white-space: pre-wrap; }
#write.fences-no-line-wrapping pre { white-space: pre; }
#write pre.ty-contain-cm { white-space: normal; }
.CodeMirror-gutters { margin-right: 4px; }
.md-fences { font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; overflow: visible; white-space: pre; background: inherit; position: relative !important; }
.md-diagram-panel { width: 100%; margin-top: 10px; text-align: center; padding-top: 0px; padding-bottom: 8px; overflow-x: auto; }
#write .md-fences.mock-cm { white-space: pre-wrap; }
.md-fences.md-fences-with-lineno { padding-left: 0px; }
#write.fences-no-line-wrapping .md-fences.mock-cm { white-space: pre; overflow-x: auto; }
.md-fences.mock-cm.md-fences-with-lineno { padding-left: 8px; }
.CodeMirror-line, twitterwidget { break-inside: avoid; }
.footnotes { opacity: 0.8; font-size: 0.9rem; margin-top: 1em; margin-bottom: 1em; }
.footnotes + .footnotes { margin-top: 0px; }
.md-reset { margin: 0px; padding: 0px; border: 0px; outline: 0px; vertical-align: top; background: 0px 0px; text-decoration: none; text-shadow: none; float: none; position: static; width: auto; height: auto; white-space: nowrap; cursor: inherit; -webkit-tap-highlight-color: transparent; line-height: normal; font-weight: 400; text-align: left; box-sizing: content-box; direction: ltr; }
li div { padding-top: 0px; }
blockquote { margin: 1rem 0px; }
li .mathjax-block, li p { margin: 0.5rem 0px; }
li { margin: 0px; position: relative; }
blockquote > :last-child { margin-bottom: 0px; }
blockquote > :first-child, li > :first-child { margin-top: 0px; }
.footnotes-area { color: rgb(136, 136, 136); margin-top: 0.714rem; padding-bottom: 0.143rem; white-space: normal; }
#write .footnote-line { white-space: pre-wrap; }
@media print {
  body, html { border: 1px solid transparent; height: 99%; break-after: avoid; break-before: avoid; }
  #write { margin-top: 0px; padding-top: 0px; border-color: transparent !important; }
  .typora-export * { -webkit-print-color-adjust: exact; }
  html.blink-to-pdf { font-size: 13px; }
  .typora-export #write { padding-left: 32px; padding-right: 32px; padding-bottom: 0px; break-after: avoid; }
  .typora-export #write::after { height: 0px; }
}
.footnote-line { margin-top: 0.714em; font-size: 0.7em; }
a img, img a { cursor: pointer; }
pre.md-meta-block { font-size: 0.8rem; min-height: 0.8rem; white-space: pre-wrap; background: rgb(204, 204, 204); display: block; overflow-x: hidden; }
p > .md-image:only-child:not(.md-img-error) img, p > img:only-child { display: block; margin: auto; }
p > .md-image:only-child { display: inline-block; width: 100%; }
#write .MathJax_Display { margin: 0.8em 0px 0px; }
.md-math-block { width: 100%; }
.md-math-block:not(:empty)::after { display: none; }
[contenteditable="true"]:active, [contenteditable="true"]:focus { outline: 0px; box-shadow: none; }
.md-task-list-item { position: relative; list-style-type: none; }
.task-list-item.md-task-list-item { padding-left: 0px; }
.md-task-list-item > input { position: absolute; top: 0px; left: 0px; margin-left: -1.2em; margin-top: calc(1em - 10px); border: none; }
.math { font-size: 1rem; }
.md-toc { min-height: 3.58rem; position: relative; font-size: 0.9rem; border-radius: 10px; }
.md-toc-content { position: relative; margin-left: 0px; }
.md-toc-content::after, .md-toc::after { display: none; }
.md-toc-item { display: block; color: rgb(65, 131, 196); }
.md-toc-item a { text-decoration: none; }
.md-toc-inner:hover { text-decoration: underline; }
.md-toc-inner { display: inline-block; cursor: pointer; }
.md-toc-h1 .md-toc-inner { margin-left: 0px; font-weight: 700; }
.md-toc-h2 .md-toc-inner { margin-left: 2em; }
.md-toc-h3 .md-toc-inner { margin-left: 4em; }
.md-toc-h4 .md-toc-inner { margin-left: 6em; }
.md-toc-h5 .md-toc-inner { margin-left: 8em; }
.md-toc-h6 .md-toc-inner { margin-left: 10em; }
@media screen and (max-width: 48em) {
  .md-toc-h3 .md-toc-inner { margin-left: 3.5em; }
  .md-toc-h4 .md-toc-inner { margin-left: 5em; }
  .md-toc-h5 .md-toc-inner { margin-left: 6.5em; }
  .md-toc-h6 .md-toc-inner { margin-left: 8em; }
}
a.md-toc-inner { font-size: inherit; font-style: inherit; font-weight: inherit; line-height: inherit; }
.footnote-line a:not(.reversefootnote) { color: inherit; }
.md-attr { display: none; }
.md-fn-count::after { content: "."; }
code, pre, samp, tt { font-family: var(--monospace); }
kbd { margin: 0px 0.1em; padding: 0.1em 0.6em; font-size: 0.8em; color: rgb(36, 39, 41); background: rgb(255, 255, 255); border: 1px solid rgb(173, 179, 185); border-radius: 3px; box-shadow: rgba(12, 13, 14, 0.2) 0px 1px 0px, rgb(255, 255, 255) 0px 0px 0px 2px inset; white-space: nowrap; vertical-align: middle; }
.md-comment { color: rgb(162, 127, 3); opacity: 0.8; font-family: var(--monospace); }
code { text-align: left; vertical-align: initial; }
a.md-print-anchor { white-space: pre !important; border-width: initial !important; border-style: none !important; border-color: initial !important; display: inline-block !important; position: absolute !important; width: 1px !important; right: 0px !important; outline: 0px !important; background: 0px 0px !important; text-decoration: initial !important; text-shadow: initial !important; }
.md-inline-math .MathJax_SVG .noError { display: none !important; }
.html-for-mac .inline-math-svg .MathJax_SVG { vertical-align: 0.2px; }
.md-math-block .MathJax_SVG_Display { text-align: center; margin: 0px; position: relative; text-indent: 0px; max-width: none; max-height: none; min-height: 0px; min-width: 100%; width: auto; overflow-y: hidden; display: block !important; }
.MathJax_SVG_Display, .md-inline-math .MathJax_SVG_Display { width: auto; margin: inherit; display: inline-block !important; }
.MathJax_SVG .MJX-monospace { font-family: var(--monospace); }
.MathJax_SVG .MJX-sans-serif { font-family: sans-serif; }
.MathJax_SVG { display: inline; font-style: normal; font-weight: 400; line-height: normal; zoom: 90%; text-indent: 0px; text-align: left; text-transform: none; letter-spacing: normal; word-spacing: normal; overflow-wrap: normal; white-space: nowrap; float: none; direction: ltr; max-width: none; max-height: none; min-width: 0px; min-height: 0px; border: 0px; padding: 0px; margin: 0px; }
.MathJax_SVG * { transition: none 0s ease 0s; }
.MathJax_SVG_Display svg { vertical-align: middle !important; margin-bottom: 0px !important; margin-top: 0px !important; }
.os-windows.monocolor-emoji .md-emoji { font-family: "Segoe UI Symbol", sans-serif; }
.md-diagram-panel > svg { max-width: 100%; }
[lang="mermaid"] svg, [lang="flow"] svg { max-width: 100%; height: auto; }
[lang="mermaid"] .node text { font-size: 1rem; }
table tr th { border-bottom: 0px; }
video { max-width: 100%; display: block; margin: 0px auto; }
iframe { max-width: 100%; width: 100%; border: none; }
.highlight td, .highlight tr { border: 0px; }


.CodeMirror { height: auto; }
.CodeMirror.cm-s-inner { background: inherit; }
.CodeMirror-scroll { overflow: auto hidden; z-index: 3; }
.CodeMirror-gutter-filler, .CodeMirror-scrollbar-filler { background-color: rgb(255, 255, 255); }
.CodeMirror-gutters { border-right: 1px solid rgb(221, 221, 221); background: inherit; white-space: nowrap; }
.CodeMirror-linenumber { padding: 0px 3px 0px 5px; text-align: right; color: rgb(153, 153, 153); }
.cm-s-inner .cm-keyword { color: rgb(119, 0, 136); }
.cm-s-inner .cm-atom, .cm-s-inner.cm-atom { color: rgb(34, 17, 153); }
.cm-s-inner .cm-number { color: rgb(17, 102, 68); }
.cm-s-inner .cm-def { color: rgb(0, 0, 255); }
.cm-s-inner .cm-variable { color: rgb(0, 0, 0); }
.cm-s-inner .cm-variable-2 { color: rgb(0, 85, 170); }
.cm-s-inner .cm-variable-3 { color: rgb(0, 136, 85); }
.cm-s-inner .cm-string { color: rgb(170, 17, 17); }
.cm-s-inner .cm-property { color: rgb(0, 0, 0); }
.cm-s-inner .cm-operator { color: rgb(152, 26, 26); }
.cm-s-inner .cm-comment, .cm-s-inner.cm-comment { color: rgb(170, 85, 0); }
.cm-s-inner .cm-string-2 { color: rgb(255, 85, 0); }
.cm-s-inner .cm-meta { color: rgb(85, 85, 85); }
.cm-s-inner .cm-qualifier { color: rgb(85, 85, 85); }
.cm-s-inner .cm-builtin { color: rgb(51, 0, 170); }
.cm-s-inner .cm-bracket { color: rgb(153, 153, 119); }
.cm-s-inner .cm-tag { color: rgb(17, 119, 0); }
.cm-s-inner .cm-attribute { color: rgb(0, 0, 204); }
.cm-s-inner .cm-header, .cm-s-inner.cm-header { color: rgb(0, 0, 255); }
.cm-s-inner .cm-quote, .cm-s-inner.cm-quote { color: rgb(0, 153, 0); }
.cm-s-inner .cm-hr, .cm-s-inner.cm-hr { color: rgb(153, 153, 153); }
.cm-s-inner .cm-link, .cm-s-inner.cm-link { color: rgb(0, 0, 204); }
.cm-negative { color: rgb(221, 68, 68); }
.cm-positive { color: rgb(34, 153, 34); }
.cm-header, .cm-strong { font-weight: 700; }
.cm-del { text-decoration: line-through; }
.cm-em { font-style: italic; }
.cm-link { text-decoration: underline; }
.cm-error { color: red; }
.cm-invalidchar { color: red; }
.cm-constant { color: rgb(38, 139, 210); }
.cm-defined { color: rgb(181, 137, 0); }
div.CodeMirror span.CodeMirror-matchingbracket { color: rgb(0, 255, 0); }
div.CodeMirror span.CodeMirror-nonmatchingbracket { color: rgb(255, 34, 34); }
.cm-s-inner .CodeMirror-activeline-background { background: inherit; }
.CodeMirror { position: relative; overflow: hidden; }
.CodeMirror-scroll { height: 100%; outline: 0px; position: relative; box-sizing: content-box; background: inherit; }
.CodeMirror-sizer { position: relative; }
.CodeMirror-gutter-filler, .CodeMirror-hscrollbar, .CodeMirror-scrollbar-filler, .CodeMirror-vscrollbar { position: absolute; z-index: 6; display: none; }
.CodeMirror-vscrollbar { right: 0px; top: 0px; overflow: hidden; }
.CodeMirror-hscrollbar { bottom: 0px; left: 0px; overflow: hidden; }
.CodeMirror-scrollbar-filler { right: 0px; bottom: 0px; }
.CodeMirror-gutter-filler { left: 0px; bottom: 0px; }
.CodeMirror-gutters { position: absolute; left: 0px; top: 0px; padding-bottom: 30px; z-index: 3; }
.CodeMirror-gutter { white-space: normal; height: 100%; box-sizing: content-box; padding-bottom: 30px; margin-bottom: -32px; display: inline-block; }
.CodeMirror-gutter-wrapper { position: absolute; z-index: 4; background: 0px 0px !important; border: none !important; }
.CodeMirror-gutter-background { position: absolute; top: 0px; bottom: 0px; z-index: 4; }
.CodeMirror-gutter-elt { position: absolute; cursor: default; z-index: 4; }
.CodeMirror-lines { cursor: text; }
.CodeMirror pre { border-radius: 0px; border-width: 0px; background: 0px 0px; font-family: inherit; font-size: inherit; margin: 0px; white-space: pre; overflow-wrap: normal; color: inherit; z-index: 2; position: relative; overflow: visible; }
.CodeMirror-wrap pre { overflow-wrap: break-word; white-space: pre-wrap; word-break: normal; }
.CodeMirror-code pre { border-right: 30px solid transparent; width: fit-content; }
.CodeMirror-wrap .CodeMirror-code pre { border-right: none; width: auto; }
.CodeMirror-linebackground { position: absolute; left: 0px; right: 0px; top: 0px; bottom: 0px; z-index: 0; }
.CodeMirror-linewidget { position: relative; z-index: 2; overflow: auto; }
.CodeMirror-wrap .CodeMirror-scroll { overflow-x: hidden; }
.CodeMirror-measure { position: absolute; width: 100%; height: 0px; overflow: hidden; visibility: hidden; }
.CodeMirror-measure pre { position: static; }
.CodeMirror div.CodeMirror-cursor { position: absolute; visibility: hidden; border-right: none; width: 0px; }
.CodeMirror div.CodeMirror-cursor { visibility: hidden; }
.CodeMirror-focused div.CodeMirror-cursor { visibility: inherit; }
.cm-searching { background: rgba(255, 255, 0, 0.4); }
@media print {
  .CodeMirror div.CodeMirror-cursor { visibility: hidden; }
}


:root { --side-bar-bg-color: #fff; --control-text-color: #777; --active-file-border-color: var(--drake-accent); --drake-accent: #e95f59; --drake-highlight: #d63200; }
html { font-size: 16px; }
body { font-family: "Source Han Sans SC", sans-serif; color: rgb(85, 85, 85); -webkit-font-smoothing: auto; line-height: 1.8rem; letter-spacing: 2px; margin: 0px; overflow-x: hidden; }
img { border-radius: 2px; }
#write { max-width: 1400px; margin: 0px auto; padding: 20px 30px 100px; }
#write p { line-height: 1.6rem; word-spacing: 0.05rem; }
#write > ul:first-child, #write > ol:first-child { margin-top: 30px; }
body > :first-child { margin-top: 0px !important; }
body > :last-child { margin-bottom: 0px !important; }
a { color: var(--drake-highlight); padding: 0px 2px; }
h1, h2, h3, h4, h5, h6 { position: relative; margin-top: 3rem; margin-bottom: 1rem; font-weight: bold; line-height: 1.4; cursor: text; color: rgb(39, 56, 73); }
h1:hover a.anchor, h2:hover a.anchor, h3:hover a.anchor, h4:hover a.anchor, h5:hover a.anchor, h6:hover a.anchor { text-decoration: none; }
h1 tt, h1 code { font-size: inherit !important; }
h2 tt, h2 code { font-size: inherit !important; }
h3 tt, h3 code { font-size: inherit !important; }
h4 tt, h4 code { font-size: inherit !important; }
h5 tt, h5 code { font-size: inherit !important; }
h6 tt, h6 code { font-size: inherit !important; }
h2 a, h3 a { color: rgb(52, 73, 94); }
h1 { padding-bottom: 0.4rem; font-size: 2.2rem; text-align: center; margin-top: 0px; line-height: 1.3; }
h2 { font-size: 1.75rem; line-height: 1.225; padding-bottom: 10px; margin-bottom: 2rem; border-bottom: 1px solid rgb(234, 236, 239); }
h3 { font-size: 1.4rem; line-height: 1.43; }
h4 { font-size: 1.2rem; }
h5 { font-size: 1rem; }
h6 { font-size: 1rem; color: rgb(119, 119, 119); }
p, blockquote, ul, ol, dl, table { margin: 0.8em 0px; }
li > ol, li > ul { margin: 0px; }
hr { height: 2px; padding: 0px; margin: 16px 0px; background-color: rgb(231, 231, 231); border: 0px none; overflow: hidden; box-sizing: content-box; }
body > h2:first-child { margin-top: 0px; padding-top: 0px; }
body > h1:first-child { margin-top: 0px; padding-top: 0px; }
body > h1:first-child + h2 { margin-top: 0px; padding-top: 0px; }
body > h3:first-child, body > h4:first-child, body > h5:first-child, body > h6:first-child { margin-top: 0px; padding-top: 0px; }
a:first-child h1, a:first-child h2, a:first-child h3, a:first-child h4, a:first-child h5, a:first-child h6 { margin-top: 0px; padding-top: 0px; }
h1 p, h2 p, h3 p, h4 p, h5 p, h6 p { margin-top: 0px; }
li p.first { display: inline-block; }
ul, ol { padding-left: 30px; }
ul:first-child, ol:first-child { margin-top: 0px; }
ul:last-child, ol:last-child { margin-bottom: 0px; }
blockquote { color: rgb(48, 68, 85); border-radius: 2px; padding: 10px 16px; background-color: rgb(253, 239, 238); position: relative; border-left: none; }
blockquote::before { display: block; position: absolute; content: ""; width: 4px; left: 0px; top: 0px; height: 100%; background-color: var(--drake-accent); border-radius: 2px; }
table { padding: 0px; word-break: initial; }
table tr { border-top: 1px solid rgb(223, 226, 229); margin: 0px; padding: 0px; }
table tr:nth-child(2n), thead { background-color: rgb(250, 250, 250); }
table tr th { font-weight: bold; border-width: 1px 1px 0px; border-top-style: solid; border-right-style: solid; border-left-style: solid; border-top-color: rgb(223, 226, 229); border-right-color: rgb(223, 226, 229); border-left-color: rgb(223, 226, 229); border-image: initial; border-bottom-style: initial; border-bottom-color: initial; text-align: left; margin: 0px; padding: 6px 13px; }
table tr td { border: 1px solid rgb(223, 226, 229); text-align: left; margin: 0px; padding: 6px 13px; }
table tr th:first-child, table tr td:first-child { margin-top: 0px; }
table tr th:last-child, table tr td:last-child { margin-bottom: 0px; }
#write em { padding: 0px 5px 0px 2px; }
#write table thead th { background-color: rgb(242, 242, 242); }
#write .CodeMirror-gutters { border-right: none; }
#write .md-fences { font-size: 1rem; border-radius: 2px; overflow-wrap: normal; background-color: rgb(43, 43, 43); color: rgb(169, 183, 198); border: none; margin-left: 2px; margin-right: 2px; padding: 0.5rem !important; font-family: inherit !important; }
#write code, tt { margin: 0px 2px; border-radius: 2px; color: var(--drake-highlight); font-family: initial !important; }
#write mark { background-color: rgb(231, 236, 243); color: inherit; border-radius: 2px; padding: 2px; margin: 0px 2px; }
#write del { padding: 1px 2px; }
.cm-s-inner .cm-link, .cm-s-inner.cm-link { color: rgb(34, 162, 201); }
.cm-s-inner .cm-string { color: rgb(34, 162, 201); }
.md-task-list-item > input { margin-left: -1.3em; }
@media print {
  html { font-size: 12px; }
  table, pre { break-inside: avoid; }
  pre { overflow-wrap: break-word; }
}
#write pre.md-meta-block { padding: 1rem; font-size: 85%; line-height: 1.45; background-color: rgb(247, 247, 247); border: 0px; border-radius: 2px; color: rgb(119, 119, 119); margin-top: 0px !important; }
.mathjax-block > .code-tooltip { bottom: 0.375rem; }
#write > h3.md-focus::before { left: -1.5625rem; top: 0.375rem; }
#write > h4.md-focus::before { left: -1.5625rem; top: 0.285714rem; }
#write > h5.md-focus::before { left: -1.5625rem; top: 0.285714rem; }
#write > h6.md-focus::before { left: -1.5625rem; top: 0.285714rem; }
.md-image > .md-meta { border-radius: 2px; font-family: initial; padding: 2px 0px 0px 4px; color: inherit; }
.md-tag { color: inherit; }
.md-toc { margin-top: 20px; padding-bottom: 20px; }
.sidebar-tabs { border-bottom: none; }
#typora-quick-open { border: 1px solid rgb(221, 221, 221); background-color: rgb(248, 248, 248); }
.typora-quick-open-item { height: 48px; padding-left: 8px; padding-top: 4px; font-size: 1rem !important; }
#md-notification::before { top: 10px; }
.on-focus-mode blockquote { border-left-color: rgba(85, 85, 85, 0.12); }
header, .context-menu, .megamenu-content, footer { font-family: initial; }
.file-node-content:hover .file-node-icon, .file-node-content:hover .file-node-open-state { visibility: visible; }
.mac-seamless-mode #typora-sidebar { font-size: 1rem; background-color: var(--side-bar-bg-color); }
.md-lang { color: rgb(180, 101, 77); }
.html-for-mac .context-menu { --item-hover-bg-color: #E6F0FE; }
span.cm-comment { color: rgb(128, 128, 128) !important; }
.ty-quick-open-category.ty-has-prev .ty-quick-open-category-title { border-top: none; }
.file-list-item-summary { font-family: initial; }
#typora-quick-open-input { margin: 8px; box-shadow: none; border-radius: 2px; }
#typora-quick-open-input input { font-size: 1rem; box-shadow: none; padding-top: 2px; padding-left: 10px; padding-right: 10px; line-height: 32px; max-height: 32px; }
.modal-dialog#typora-quick-open { border-radius: 2px; }
.ty-quick-open-category-title { padding-left: 8px; color: rgb(190, 190, 190); }
.typora-quick-open-item-path { font-size: 10px; text-overflow: ellipsis; white-space: nowrap; }
.outline-item { font-size: 1rem; }
.sidebar-tab { font-size: 1rem; }
#write .md-footnote { background-color: inherit; color: var(--drake-highlight); font-size: 0.9rem; border-radius: 0.9rem; padding-left: 0px; }
#write .md-footnote::before { content: "["; }
#write .md-footnote::after { content: "]"; }
.md-hover-tip .code-tooltip-content { border-radius: 2px; }
span.md-def-name { padding-right: 3ch; padding-left: 0px; position: relative; font-weight: normal; }
.footnotes { font-size: 1rem; font-weight: normal; color: rgb(48, 68, 85); position: relative; }
.code-tooltip-content .md-plain { font-size: 0.9rem; font-family: inherit; }
.code-tooltip-content code { padding: 0px 2px; font-family: inherit; color: rgb(255, 215, 96); background-color: inherit; }
.code-tooltip-content a { color: rgb(255, 215, 96); }
div.code-tooltip-content { box-shadow: rgba(0, 0, 0, 0.5) 0px 0px 8px; }
.footnotes { opacity: 1; }
.md-def-name::after { content: ". ^"; color: rgb(48, 68, 85); }
.md-def-footnote .md-def-name::before { content: ""; color: rgb(48, 68, 85); position: absolute; }
.md-def-name::before { content: ""; color: rgb(48, 68, 85); position: absolute; }
.CodeMirror-scroll::-webkit-scrollbar { display: none; }
.file-list-item-summary { font-size: 1em; }
.pin-outline #outline-content .outline-active strong, .pin-outline .outline-active { font-weight: 500; color: var(--drake-highlight); }
.file-list-item.active { border-left: 4px solid var(--drake-accent); }
.cm-s-inner.CodeMirror { background: rgb(43, 43, 43); color: rgb(169, 183, 198); }
.cm-s-inner span.cm-meta { color: rgb(187, 181, 41); }
.cm-s-inner span.cm-number { color: rgb(104, 151, 187); }
.cm-s-inner span.cm-keyword { color: rgb(204, 120, 50); }
.cm-s-inner span.cm-def { color: rgb(255, 215, 96); }
.cm-s-inner span.cm-variable { color: rgb(169, 183, 198); }
.cm-s-inner span.cm-variable-2 { color: rgb(169, 183, 198); }
.cm-s-inner span.cm-variable-3 { color: rgb(152, 118, 170); }
.cm-s-inner span.cm-type { color: rgb(170, 187, 204); }
.cm-s-inner span.cm-property { color: rgb(255, 198, 109); }
.cm-s-inner span.cm-operator { color: rgb(169, 183, 198); }
.cm-s-inner span.cm-string { color: rgb(106, 135, 89); }
.cm-s-inner span.cm-string-2 { color: rgb(106, 135, 89); }
.cm-s-inner span.cm-comment { color: rgb(97, 161, 81); }
.cm-s-inner span.cm-link { color: rgb(204, 120, 50); }
.cm-s-inner span.cm-atom { color: rgb(204, 120, 50); }
.cm-s-inner span.cm-error { color: rgb(188, 63, 60); }
.cm-s-inner span.cm-tag { color: rgb(232, 191, 106); }
.cm-s-inner span.cm-quote { color: rgb(166, 226, 46); }
.cm-s-inner span.cm-attribute { color: rgb(152, 118, 170); }
.cm-s-inner span.cm-qualifier { color: rgb(106, 135, 89); }
.cm-s-inner span.cm-bracket { color: rgb(232, 191, 106); }
.cm-s-inner span.cm-builtin { color: rgb(255, 158, 89); }
.cm-s-inner span.cm-special { color: rgb(255, 158, 89); }
.cm-s-inner span.cm-matchhighlight { color: rgb(255, 255, 255); background-color: rgba(50, 89, 48, 0.7); font-weight: normal; }
.cm-s-inner span.cm-searching { color: rgb(255, 255, 255); background-color: rgba(61, 115, 59, 0.7); font-weight: normal; }
.cm-s-inner .CodeMirror-cursor { border-left: 1px solid rgb(169, 183, 198); }
.cm-s-inner .CodeMirror-gutters { background: rgb(49, 51, 53); border-right: 1px solid rgb(49, 51, 53); }
.cm-s-inner .CodeMirror-guttermarker { color: rgb(255, 238, 128); }
.cm-s-inner .CodeMirror-guttermarker-subtle { color: rgb(208, 208, 208); }
.cm-s-inner .CodeMirrir-linenumber { color: rgb(96, 99, 102); }
.cm-s-inner .CodeMirror-matchingbracket { background-color: rgb(59, 81, 77); color: rgb(255, 239, 40) !important; }
.cm-s-inner .CodeMirror-selected { background: rgb(33, 66, 131) !important; }
.cm-s-inner .CodeMirror-selectedtext { background: rgb(33, 66, 131) !important; }
.cm-overlay.CodeMirror-selectedtext { background: rgb(181, 214, 252) !important; }
.cm-s-inner div.CodeMirror-cursor { border-left: 1px solid rgb(255, 255, 255); }

 .typora-export li, .typora-export p, .typora-export,  .footnote-line {white-space: normal;} 
</style>
</head>
<body class='typora-export os-windows' >
<div  id='write'  class = 'is-node'><h2><a name="%E5%9F%BA%E7%A1%80%E7%AB%A0%E8%8A%82-01-mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%8D%E5%8A%A1%E4%B8%AD%E7%BA%A7%E8%AF%BE%E7%A8%8B" class="md-header-anchor"></a><span>基础章节-01-MySQL数据库服务中级课程</span></h2><p><img src='./%E5%8A%A8%E7%94%BB.gif' alt='' referrerPolicy='no-referrer' /></p><h3><a name="1.00-%E8%AF%BE%E7%A8%8B%E7%9F%A5%E8%AF%86%E7%AB%A0%E8%8A%82%E8%AF%B4%E6%98%8E" class="md-header-anchor"></a><span>1.00 课程知识章节说明</span></h3><p><span>目前在互联网的实际应用中，各个企业都会比较关注自身网站的数据信息，既要保证数据信息的安全性，同时也要保证数据存储读取效率</span></p><p><span>并且在特殊的场景下，还要对存储的数据信息进行检索和分析；因此数据库服务业务已经在各行各业应用非常的广泛</span></p><p><span>对于互联网领域的技术人员，对于数据库服务知识的掌握，也将是在求职时必备的技能，有些时候还会绝对入职的定级和薪资水平。  </span></p><p>&nbsp;</p><h3><a name="1.04-%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%8D%E5%8A%A1%E4%BD%93%E7%B3%BB%E7%BB%93%E6%9E%84" class="md-header-anchor"></a><span>1.04 数据库服务体系结构</span></h3><h4><a name="1.4.1-%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%8D%E5%8A%A1%E5%B7%A5%E4%BD%9C%E6%A8%A1%E5%9E%8B" class="md-header-anchor"></a><span>1.4.1 数据库服务工作模型</span></h4><p><span>数据库服务在实际应用过程中，是采用C/S（客户端/服务端）模型方式进行工作的；并利用socket（套接字）与TCP/IP建立连接通信；</span></p><p><img src='./1673282572894.png' alt='1673282572894' referrerPolicy='no-referrer' /></p><h4><a name="1.4.2-%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%8D%E5%8A%A1%E5%AE%9E%E4%BE%8B%E6%9E%84%E6%88%90" class="md-header-anchor"></a><span>1.4.2 数据库服务实例构成</span></h4><p><span>数据库服务实例就是程序运行工作的一种方式，会占用一定的内存资源和CPU资源，并且会派生出多个线程完成不同的任务需求；</span></p><p><code>MySQL实例 = mysqld + master thread监控管理 + 具体干活的thread + 预分配的内存结构</code></p><p><span>简单来说：mysql实例就是占用内存资源的统称，利用mysql实例可以对数据进行处理；</span></p><p><span>实例工作示意图：</span></p><p><img src='./1667103513248.png' alt='1667103513248' referrerPolicy='no-referrer' /></p><h4><a name="1.4.3-%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%8D%E5%8A%A1%E7%A8%8B%E5%BA%8F%E7%BB%93%E6%9E%84" class="md-header-anchor"></a><span>1.4.3 数据库服务程序结构</span></h4><p><span>本知识点内容将和大家聊聊MySQL数据库服务的基础架构。在学习过程中，经常建议学生千万不要直接陷入细节里，应该鸟瞰全貌；</span></p><p><span>这样能够帮助初学者从高维度理解问题，同样，对于MySQL的学习也是这样，平时操作使用数据库时，看到的通常都是一个整体；</span></p><p><span>比如：有个最简单的数据表，表里有一个ID字段，在执行下面的操作查询语句时：</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="sql"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 35px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 31px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -31px; width: 31px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; <span class="cm-keyword">select</span> * <span class="cm-keyword">from</span> T <span class="cm-keyword">where</span> ID=<span class="cm-number">10</span>；</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 36px;"></div><div class="CodeMirror-gutters" style="height: 36px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 31px;"></div></div></div></div></pre><p><span>看到的只是输入一条语句，返回一个结果，却不知道这条语句在MySQL内部的执行过程；</span></p><p><span>所以本节知识内容会将MySQL服务程序拆解一下，看看里面都有哪些&quot;零件&quot;组成，通过拆解过程，从而对MySQL有更输入的理解；</span></p><p><span>使以后在使用MySQL服务在遇到一些异常或者问题时，能够直戳本质，更为快速地定位并解决问题；</span></p><p><span>下面图示是MySQL基本架构示意图，从中可以清楚看到SQL语句在MySQL的各个功能模块中的执行过程：</span></p><p><img src='./1680019373792.png' alt='1680019373792' referrerPolicy='no-referrer' /></p><p><span>大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。</span></p><p><strong><span>服务层（Server）：</span></strong></p><p><span>Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能；</span></p><p><span>以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。</span></p><p>&nbsp;</p><p><strong><span>引擎层（Engine）：</span></strong></p><p><span>存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。</span></p><p><span>现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。</span></p><p><span>也就是说，在执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。</span></p><p><span>也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语句中使用 engine=memory, 来指定使用内存引擎创建表；</span></p><p><span>不同存储引擎的表数据存取方式不同，支持的功能也不同，在后面的文章中，会讨论到引擎的选择。</span></p><p>&nbsp;</p><p><span>从图中不难看出，不同的存储引擎共用一个 Server 层，也就是从连接器到执行器的部分。</span></p><p><span>可以先对每个组件的名字有个印象，接下来我会结合上面提到的 SQL 语句，过一遍语句整个执行流程</span></p><blockquote><p><span>说明：大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。</span></p></blockquote><p>&nbsp;</p><p><span>在使用mysql数据库服务完成工作任务时，也需要了解数据库服务整体的工作逻辑，也便于在数据库服务出现异常时，从底层分析问题；</span></p><p><span>程序工作原理逻辑示意图：客户端执行SQL -&gt; 客户端收到请求数据信息，整个过程中都经历了什么？</span></p><p><span>图形架构解读分析：</span></p><p><img src='./1667115780477.png' alt='1667115780477' referrerPolicy='no-referrer' /></p><p><strong><span>连接器（数据库服务连接层）：</span></strong></p><p><span>第一步，语句执行前会先连接到数据库服务上，这时候处理连接请求的就是连接器；</span></p><p><span>连接器负责和客户端建立连接、获取权限、维持和管理连接，连接请求命令一般书写为：</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="sql"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 35px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 31px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -31px; width: 31px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql -h$ip -P$port -u$user -p</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 36px;"></div><div class="CodeMirror-gutters" style="height: 36px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 31px;"></div></div></div></div></pre><p><span>输完命令之后，就需要在交互对话里面输入密码。虽然密码也可以直接跟在 -p 后面写在命令行中，但这样可能会导致你的密码泄露。</span></p><p><span>如果连接的是生产服务器，强烈建议不要这么做。</span></p><p><span>连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证客户端的身份；</span></p><p><span>这个时候验证的就是输入的用户名和密码。</span></p><ul><li><p><span>如果用户名密码验证失败：</span></p><p><span>就会收到一个&quot;</span><code>Access denied for user</code><span>&quot;的错误，然后客户端程序结束执行。</span></p></li><li><p><span>如果用户名密码验证成功：</span></p><p><span>连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。</span></p></li></ul><p><span>这就意味着，</span><u><span>一个用户成功建立连接后，即使用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限</span></u><span>。</span></p><p><u><span>修改完成后，只有再新建的连接才会使用新的权限设置</span></u><span>。</span></p><p>&nbsp;</p><p><span>连接完成后，如果没有后续的动作，这个连接就处于空闲状态，可以在 </span><code>show processlist</code><span> 命令中看到它。</span></p><p><span>其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接</span></p><p><span>查询数据库服务连接进程信息：</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="tiki wiki"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="tiki wiki"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 35px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 31px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -31px; width: 31px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql &gt; show processlist;</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 36px;"></div><div class="CodeMirror-gutters" style="height: 36px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 31px;"></div></div></div></div></pre><p><img src='./1667105177005.png' alt='1667105177005' referrerPolicy='no-referrer' /></p><p>&nbsp;</p><p><span>客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 </span><code>wait_timeout</code><span> 控制的，默认值是 8 小时。</span></p><p><span>如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 35px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 31px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -31px; width: 31px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">Lost connection to MySQL server during query</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 36px;"></div><div class="CodeMirror-gutters" style="height: 36px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 31px;"></div></div></div></div></pre><p><span>这时候如果你要继续，就需要重连，然后再执行请求了。</span></p><p>&nbsp;</p><p><strong><span>数据库里面网络连接方式：</span></strong></p><ul><li><span>长连接：是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。</span></li><li><span>短连接：则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。</span></li></ul><blockquote><p><span>说明：建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。</span></p></blockquote><p>&nbsp;</p><p><strong><span>数据库里面网络连接问题：</span></strong></p><p><span>在数据库使用过程中，全部使用长连接后，可能会发现，有些时候 MySQL 占用内存涨得特别快；</span></p><p><span>这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。</span></p><p><span>所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。</span></p><p><span>怎么解决这个问题呢？你可以考虑以下两种方案：</span></p><ol><li><p><span>定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。</span></p></li><li><p><span>如果用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后；</span></p><p><span>通过执行 </span><code>mysql_reset_connection</code><span> 来重新初始化连接资；</span></p><p><span>这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态；</span></p></li></ol><p>&nbsp;</p><p><span>查询数据库服务并发连接数量：</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="tiki wiki" style="break-inside: unset;"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="tiki wiki"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 48px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 44px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>14</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -44px; width: 44px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 36px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; select @@max_connections;</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tw-listitem cm-bracket">+----------------------------+</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">| @@max_connections |</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tw-listitem cm-bracket">+----------------------------+</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 151 |</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tw-listitem cm-bracket">+----------------------------+</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">1 row in set (0.00 sec)</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-error cm-tw-deleted">-- 默认情况下，数据库服务的连接线程最大数量值为151，但实际是152个连接数，会额外预留一个让本地管理员进行连接；</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="">​</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 36px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tw-listitem cm-bracket"># 临时调整最大并发连接数</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; set global max_connections=5；</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-variable-3">[root@xiaoQ-01 ~]</span># mysql</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 36px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">ERROR 1040 (HY000): Too many connections</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -44px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 36px;">14</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-error cm-tw-deleted">-- 一旦实际并发连接数=max_connections+1数量，再次连接数据库服务，就会显示超过连接数上限提示</span></span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 504px;"></div><div class="CodeMirror-gutters" style="height: 504px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 44px;"></div></div></div></div></pre><p>&nbsp;</p><p><strong><span>查询缓存</span></strong></p><p><span>连接建立完成后，就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。</span></p><p><span>MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。</span></p><p><span>之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。</span></p><p><u><code>key 是查询的语句，value 是查询的结果；</code></u></p><ul><li><span>如果查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。</span></li><li><span>如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。</span></li></ul><p><span>综上所述，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。</span></p><blockquote><p><span>说明：大多数情况下建议不要使用查询缓存，因为查询缓存往往弊大于利。</span></p></blockquote><p>&nbsp;</p><p><span>查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空；</span></p><p><span>因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了；</span></p><p><span>对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次；</span></p><p><span>比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。</span></p><p><span>MySQL 提供了一种“按需使用”的方式：</span></p><p><span>可以将参数 </span><code>query_cache_type</code><span> 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。</span></p><p><span>而对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="sql"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 35px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 31px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -31px; width: 31px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; <span class="cm-keyword">select</span> SQL_CACHE * <span class="cm-keyword">from</span> T <span class="cm-keyword">where</span> ID=<span class="cm-number">10</span>；</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 36px;"></div><div class="CodeMirror-gutters" style="height: 36px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 31px;"></div></div></div></div></pre><blockquote><p><span>需要注意的是：MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。</span></p></blockquote><p>&nbsp;</p><p><strong><span>分析器</span></strong></p><p><span>如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。</span></p><p><u><span>分析器先会做“词法分析”:</span></u></p><p><span>你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。</span></p><p><span>MySQL 从你输入的&quot;select&quot;这个关键字识别出来，这是一个查询语句。</span></p><p><span>它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。</span></p><p><u><span>做完了这些识别以后，就要做“语法分析”:</span></u></p><p><span>根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。</span></p><p><span>如果你的语句不对，就会收到“</span><code>You have an error in your SQL syntax</code><span>”的错误提醒;</span></p><p><span>比如下面这个语句 select 少打了开头的字母“s”:</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="sql"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 35px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 31px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>2</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -31px; width: 31px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; elect * <span class="cm-keyword">from</span> t <span class="cm-keyword">where</span> ID=<span class="cm-number">1</span>;</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">ERROR <span class="cm-number">1064</span> (<span class="cm-number">42000</span>): You have an error <span class="cm-keyword">in</span> your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near <span class="cm-string">'elect * from t where ID=1'</span> at line <span class="cm-number">1</span></span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 108px;"></div><div class="CodeMirror-gutters" style="height: 108px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 31px;"></div></div></div></div></pre><p><span>一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。</span></p><p>&nbsp;</p><p><strong><span>优化器</span></strong></p><p><span>经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。</span></p><p><span>优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。</span></p><p><span>比如你执行下面这样的语句，这个语句是执行两个表的 join：</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="sql"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 35px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 31px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -31px; width: 31px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; <span class="cm-keyword">select</span> * <span class="cm-keyword">from</span> t1 <span class="cm-keyword">join</span> t2 using(ID) &nbsp;<span class="cm-keyword">where</span> t1<span class="cm-variable-2">.c</span>=<span class="cm-number">10</span> <span class="cm-keyword">and</span> t2<span class="cm-variable-2">.d</span>=<span class="cm-number">20</span>;</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 36px;"></div><div class="CodeMirror-gutters" style="height: 36px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 31px;"></div></div></div></div></pre><ul><li><span>既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。</span></li><li><span>也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。</span></li></ul><p><span>这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。</span></p><p><span>优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。</span></p><p><span>如果还有疑问，比如优化器是怎么选择索引的，有没有可能选择错等等，没关系，会在后面的文章中单独展开说明优化器的内容。</span></p><p>&nbsp;</p><p><strong><span>执行器</span></strong></p><p><span>MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。</span></p><p><span>开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误；</span></p><p><span>在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限：</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="SQL"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 35px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 31px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>2</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -31px; width: 31px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; <span class="cm-keyword">select</span> * <span class="cm-keyword">from</span> T <span class="cm-keyword">where</span> ID=<span class="cm-number">10</span>;</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">ERROR <span class="cm-number">1142</span> (<span class="cm-number">42000</span>): <span class="cm-keyword">SELECT</span> command denied to user <span class="cm-string">'b'</span>@<span class="cm-string">'localhost'</span> for <span class="cm-keyword">table</span> <span class="cm-string">'T'</span></span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 72px;"></div><div class="CodeMirror-gutters" style="height: 72px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 31px;"></div></div></div></div></pre><p><span>如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。</span></p><p><span>比如这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：</span></p><ol><li><span>调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；</span></li><li><span>调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。</span></li><li><span>执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。</span></li></ol><p><span>至此，这个语句就执行完成了。</span></p><p><span>对于有索引的表，执行的逻辑也差不多；</span></p><p><span>第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的；</span></p><p><span>会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。</span></p><p><span>这个值就是在执行器每次调用引擎获取数据行的时候累加的。</span></p><p><span>在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。</span></p><p><span>后面会专门有相关文章来讲存储引擎的内部机制，里面会有详细的说明。</span></p><p>&nbsp;</p><p><strong><span>数据库SQL语句执行逻辑思考：</span></strong></p><p><span>如果表 T 中没有字段 k，而你执行了这个语句：</span></p><pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="sql"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 0px; left: 35px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 31px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -31px; width: 31px;"></div><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -31px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 23px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> * <span class="cm-keyword">from</span> T <span class="cm-keyword">where</span> k=<span class="cm-number">1</span></span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 36px;"></div><div class="CodeMirror-gutters" style="height: 36px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 31px;"></div></div></div></div></pre><p><span>肯定是会报“不存在这个列”的错误： </span><code>“Unknown column‘k’ in‘where clause’”</code><span>。</span></p><p><span>你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？</span></p><p><u><span>分析器。MySQL会像Oracle数据库那样，在分析阶段判断语句是否正确，表是否存在，列是否存在等；</span></u></p><p>&nbsp;</p><p><span>数据库存储引擎结构信息说明：</span></p><figure><table><thead><tr><th><span>序号</span></th><th><span>结构信息</span></th><th><span>解释说明</span></th></tr></thead><tbody><tr><td><span>01</span></td><td><span>数据存储段概念（segment）</span></td><td><span>主要由多个区/簇构成</span></td></tr><tr><td><span>02</span></td><td><span>数据存储区/簇概念（extent）</span></td><td><span>主要由多个页组成，默认大小为：64page（连续的）等价于1M大小</span></td></tr><tr><td><span>03</span></td><td><span>数据存储页概念（page）</span></td><td><span>主要用于存储数据库表中的数据行信息，默认大小为16kB</span></td></tr></tbody></table></figure><blockquote><p><span>解释说明：数据库服务存储的页 区 段概念</span></p><p><span>从系统的磁盘管理概念可以得知，磁盘物理组成是由磁头，盘片，存储颗粒，并利用正负极磁化存储颗粒形成正负磁化的存储颗粒；</span></p><p><span>由于希望能连续的读取和存储数据，随即便有了磁道的概念，为便于划分磁道的区域空间，便有磁盘扇区概念（磁道上的存储空间）；</span></p><p><span>磁盘出现扇区后，便有了数据存储空间大小的概念；</span></p><p><span>在使用磁盘时，会进行分区-格式化（inode/block 4kB）-挂载后才能使用，mysql服务会使用挂载后的存储目录；</span></p><p><span>所以，数据库服务存储数据时，会使用磁盘上block进行数据存储，所以存储page=4个连续的block（8个扇区 512字节）</span></p></blockquote><p>&nbsp;</p><p>&nbsp;</p></div>
</body>
</html>